
package com.huizhan.web.dao;
 
import org.springframework.data.domain.*;
import org.springframework.data.jpa.repository.*;
import org.springframework.stereotype.Repository;
import java.util.*;
import com.huizhan.web.model.*;
import com.huizhan.web.view.*;

@Repository 
public interface AccessRecordDao extends JpaRepository<AccessRecord,Long>
{    
    /**
     * 根据项目id获取通行记录集合
     * @param projectId 项目ID
     * @return 通行记录集合
     */
    List<AccessRecord> findByProjectId(String projectId);
    
    /**
     * 根据票类型id获取通行记录集合
     * @param ticketTypeId 票类型ID
     * @return 通行记录集合
     */
    List<AccessRecord> findByTicketTypeId(Long ticketTypeId);
    
    /**
     * 根据证件类型id获取通行记录集合
     * @param documentType 证件类型ID
     * @return 通行记录集合
     */
    List<AccessRecord> findByDocumentTypeId(Long documentTypeId);

     /**
      * 根据项目id获取通行记录
      * @param projectId  项目id
      * @return 数量
      */
    List<AccessRecord> findByProjectIdAndCode(String projectId,String code);
    
    /**
     * 
     * @param projectId
     * @param code
     * @return
     */
    @Query(value="SELECT  count(1) as number,door,type_name,date_format(create_date,'%m-%d %H') as time,COUNT(document_id >0)as document,COUNT(ticket_id >0)as ticket FROM access_record WHERE project_id=?1 AND  unix_timestamp(create_date) between  ?3 and ?4 AND (door LIKE CONCAT('%',?2,'%') OR ?2  = '') AND (type_name LIKE CONCAT('%',?5,'%') OR ?5  = '') GROUP BY door,type_name,date_format(create_date,'%m-%d %H') UNION SELECT count(1) as number,door,type_name,date_format(create_date,'%m-%d') as time,COUNT(document_id >0)as document,COUNT(ticket_id >0)as ticket FROM access_record WHERE project_id=?1  AND  unix_timestamp(create_date) between  ?3 and ?4 AND (door LIKE CONCAT('%',?2,'%') OR ?2  = '') AND (type_name LIKE CONCAT('%',?5,'%') OR ?5  = '') GROUP BY door,type_name,date_format(create_date,'%m-%d') order by time,door",nativeQuery=true)
    List<Object> findByWhere(String projectId,String door,Long startDate,Long endDate,String typeName);


    /**
     * 
     * @param projectId
     * @param code
     * @return
     */
    @Query(value="SELECT count(1) FROM access_record where project_Id =?1 and code = ?2 and to_days(create_date) = to_days(now())",nativeQuery=true)
    Integer countByProjectIdAndCode(String projectId,String code);

    /**
     * 分页查证件记录集合
     * @param name 名称
     * @param phone 手机号
     * @param document_Type_Id 证件类型ID
     * @param project_Id 项目ID
     * @param pageable 分页参数
     * @return 证件记录集合
     */
    @Query(
        value = "SELECT * FROM access_record "
            + " WHERE document_Type_Id>0 AND project_Id =?3"
            + " AND (document_Type_Id =?4 OR ?4 = 0 )"
            + " AND (project_door_Id =?5 OR ?5 = 0 )"
            + " AND (code LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (id_Number LIKE CONCAT('%',?6,'%') OR ?6  = '' )", 
        countQuery = "SELECT COUNT(1) FROM access_record "
            + " WHERE document_Type_Id>0 AND project_Id =?3"
            + " AND (document_Type_Id =?4 OR ?4 = 0 )"
            + " AND (project_door_Id =?5 OR ?5 = 0 )"
            + " AND (code LIKE CONCAT('%',?2,'%') OR ?2  = '' )"
            + " AND (name LIKE CONCAT('%',?1,'%') OR ?1  = '')"
            + " AND (id_Number LIKE CONCAT('%',?6,'%') OR ?6  = '' )",
        nativeQuery = true
    )
    Page<AccessRecord> findByWhere(String name,String code,String project_Id,Long document_Type_Id,Long project_door_Id,String id_Number,Pageable pageable);
    
    /**
     * 分页查证件记录集合
     * @param name 名称
     * @param phone 手机号
     * @param Ticket_Type_Id 票类型ID
     * @param project_Id 项目ID
     * @param pageable 分页参数
     * @return 证件记录集合
     */
    @Query(
        value = "SELECT * FROM access_record"
            + " WHERE ticket_Type_Id>0 AND (project_Id =?4 )"
            + " AND (ticket_Type_Id =?1 OR ?1 = 0 )"
            + " AND (project_door_Id =?5 OR ?5 = 0 )"
            + " AND (code LIKE CONCAT('%',?3,'%') OR ?3  = '' )"
            + " AND (name LIKE CONCAT('%',?2,'%') OR ?2  = '')"
            + " AND (id_Number LIKE CONCAT('%',?6,'%') OR ?6  = '' )", 
        countQuery = "SELECT COUNT(1) FROM access_record"
            + " WHERE ticket_Type_Id>0 AND (project_Id =?4 )"
            + " AND (ticket_Type_Id =?1 OR ?1 = 0 )"
            + " AND (project_door_Id =?5 OR ?5 = 0 )"
            + " AND (code LIKE CONCAT('%',?3,'%') OR ?3  = '' )"
            + " AND (name LIKE CONCAT('%',?2,'%') OR ?2  = '')"
            + " AND (id_Number LIKE CONCAT('%',?6,'%') OR ?6  = '' )",
        nativeQuery = true
    )
    Page<AccessRecord> findByWhere(Long ticket_Type_Id,String name,String code,String project_Id,Long project_door_Id,String id_Number,Pageable pageable);
}
